package com.unittec.drainage.provider.excel.utils;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.lang.annotation.Annotation;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.ParameterizedType;
import java.lang.reflect.Type;
import java.math.BigDecimal;
import java.text.ParseException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collections;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.concurrent.ConcurrentHashMap;

import com.unittec.drainage.provider.entity.ImportTaskEntity;
import com.unittec.drainage.provider.utils.DateUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.EncryptedDocumentException;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.DataValidation;
import org.apache.poi.ss.usermodel.DataValidationConstraint;
import org.apache.poi.ss.usermodel.DataValidationHelper;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Name;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.XSSFDataValidation;
import org.springframework.util.ReflectionUtils;

import com.unittec.drainage.provider.excel.annotation.Excel;
import com.unittec.drainage.provider.excel.annotation.ExcelCell;
import com.unittec.drainage.sdk.root.internal.exception.BusinessException;

/**
 * @author ci.ren
 */
public class ExcelUtil {

    private static final Map<String, Map<ExcelCell, Field>> typeClassFieldCache = new ConcurrentHashMap<>();
    private static final Map<String, Map<ExcelCell, Map<String, String>>> typeClassFieldFormatCache =
        new ConcurrentHashMap<>();

    /**
     * 异步导入
     *
     * @param inputStream
     * @param excelImportConsumer
     * @throws InstantiationException
     * @throws IllegalAccessException
     * @throws IOException
     */
    public static <T extends ExcelRow> int importExcelAsync(InputStream inputStream,
                                                            IExcelImportConsumer<T> excelImportConsumer, ImportTaskEntity importTask, Map<String, Object> paramMap,
                                                            File resultFile) throws ClassNotFoundException, InstantiationException, IllegalAccessException, IOException {
        Workbook workbook;
        try {
            workbook = WorkbookFactory.create(inputStream);
        } catch (EncryptedDocumentException e1) {
            throw new BusinessException("文件格式错误");
        }

        try {
            return parseExcelAsync(workbook, excelImportConsumer, importTask, paramMap);
        } finally {
            FileOutputStream fos = new FileOutputStream(resultFile);
            workbook.write(fos);
            fos.flush();
            fos.close();
            workbook.close();
        }

    }

    /**
     * 获取所有数据的行数
     *
     * @param file
     * @param typeClass
     * @return
     */
    public static int getTotalRow(File file, Class<? extends ExcelRow> typeClass) throws Exception {
        try {
            return getTotalRow(new FileInputStream(file), typeClass);
        } catch (FileNotFoundException e) {
            throw new BusinessException("获取总行数失败", e);
        }
    }

    /**
     * 获取所有数据的行数
     *
     * @param inputStream
     * @param typeClass
     * @return
     */
    public static int getTotalRow(InputStream inputStream, Class<? extends ExcelRow> typeClass) throws Exception {
        Workbook workbook;
        try {
            workbook = WorkbookFactory.create(inputStream);
        } catch (EncryptedDocumentException e1) {
            throw new BusinessException("文件格式错误");
        } catch (IOException e) {
            throw new BusinessException(e);
        }

        try {
            Sheet sheet = workbook.getSheetAt(0);
            Excel excel = Class.forName(typeClass.getName()).getAnnotation(Excel.class);
            int totalRow = (sheet.getLastRowNum() - excel.firstDataRow()) + 1;
            return totalRow;
        } finally {
            try {
                workbook.close();
            } catch (IOException e) {
                throw new BusinessException("获取总行数失败", e);
            }
        }
    }

    /**
     * 获取所有数据, 首条数据所在行，从0开始
     *
     * @return
     * @throws EncryptedDocumentException
     * @throws IOException
     * @throws IllegalAccessException
     * @throws InvocationTargetException
     * @throws InstantiationException
     * @throws InvalidFormatException
     * @throws NoSuchMethodException
     * @throws ParseException
     */
    public static <T extends ExcelRow> List<T> getDataList(InputStream inputStream, Class<T> typeClass)
        throws Exception {

        Workbook workbook;
        try {
            workbook = WorkbookFactory.create(inputStream);
        } catch (EncryptedDocumentException e1) {
            throw new BusinessException("文件格式错误");
        } catch (IOException e) {
            throw new BusinessException(e);
        }

        try {
            int sheetIndex = 0;

            Sheet sheet = workbook.getSheetAt(sheetIndex);
            Map<String, Integer> titleMap = getTitleMap(sheet, typeClass);
            Map<ExcelCell, Field> propertyMap = getFieldMap(typeClass);
            Map<ExcelCell, Map<String, String>> propertyFormatMap = getFieldFormatMap(typeClass);

            List<T> dataList = new ArrayList<>();

            Excel excel = Class.forName(typeClass.getName()).getAnnotation(Excel.class);
            int lastRowNum = sheet.getLastRowNum();
            for (int i = excel.firstDataRow(); i <= lastRowNum; i++) {
                Row row = sheet.getRow(i);
                T t = parseRow(row, typeClass, propertyMap, propertyFormatMap, titleMap);
                dataList.add(t);
            }
            return dataList;
        } finally {
            try {
                workbook.close();
            } catch (IOException e) {
                throw new BusinessException("解析数据失败", e);
            }
        }
    }

    /**
     * 解析文件执行异步导入
     *
     * @param workbook
     * @param excelImportConsumer
     * @return 成功条数
     * @throws IOException
     * @throws InstantiationException
     * @throws IllegalAccessException
     */
    public static <T extends ExcelRow> int parseExcelAsync(Workbook workbook,
        IExcelImportConsumer<T> excelImportConsumer, ImportTaskEntity importTask, Map<String, Object> paramMap)
        throws IOException, InstantiationException, IllegalAccessException, ClassNotFoundException {

        String typeClassStr = String.valueOf(paramMap.get("typeClass"));
        int successful = 0;

        Class typeClass = Class.forName(typeClassStr);

        int sheetIndex = 0;

        Sheet sheet = workbook.getSheetAt(sheetIndex);

        Map<String, Integer> titleMap = getTitleMap(sheet, typeClass);
        checkTemplate(titleMap, typeClass);

        int lastCellNum = 0;
        if (titleMap != null) {
            for (Entry<String, Integer> entry : titleMap.entrySet()) {
                if (entry.getValue() != null) {
                    if (entry.getValue().intValue() > lastCellNum) {
                        lastCellNum = entry.getValue().intValue();
                    }
                }
            }
        }

        Map<ExcelCell, Field> propertyMap = getFieldMap(typeClass);
        Map<ExcelCell, Map<String, String>> propertyFormatMap = getFieldFormatMap(typeClass);

        List<T> successList = new ArrayList<>();
        Excel excel = Class.forName(typeClass.getName()).getAnnotation(Excel.class);
        int lastRowNum = sheet.getLastRowNum();
        int beginIndex = excel.firstDataRow();
        if (importTask.getLastRowIndex() != null && importTask.getLastRowIndex() > 0) {
            beginIndex = importTask.getLastRowIndex();
        }
        for (int i = beginIndex; i <= lastRowNum; i++) {
            Row row = sheet.getRow(i);
            T t = (T)parseRow(row, typeClass, propertyMap, propertyFormatMap, titleMap);
            if (t.getErrorMessage() != null) {
                Cell cell = row.createCell(lastCellNum + 1, CellType.STRING);
                cell.setCellValue(t.getErrorMessage());
            } else {
                successList.add(t);
            }

            if ((!successList.isEmpty()) && successList.size() % 100 == 0) {
                int count = excelImportConsumer.consume(successList, importTask, paramMap);
                successful += count;
                writeErrorMessage(sheet, successList, titleMap, workbook);
                successList.clear();
            }
        }

        if (!successList.isEmpty()) {
            int count = excelImportConsumer.consume(successList, importTask, paramMap);
            successful += count;
            writeErrorMessage(sheet, successList, titleMap, workbook);
        }

        return successful;

    }

    public static boolean isRowEmpty(Row row) {
        for (int c = row.getFirstCellNum(); c < row.getLastCellNum(); c++) {
            Cell cell = row.getCell(c);
            if (cell != null && cell.getCellType() != CellType.BLANK) {
                return false;
            }
        }
        return true;

    }

    private static final Object locker = new Object();

    public static <T extends ExcelRow> Map<ExcelCell, Field> getFieldMap(Class<T> typeClass)
        throws InstantiationException, IllegalAccessException, ClassNotFoundException {
        Map<ExcelCell, Field> propertyMap = typeClassFieldCache.get(typeClass.getName());
        if (propertyMap == null || propertyMap.isEmpty()) {
            synchronized (locker) {
                propertyMap = typeClassFieldCache.get(typeClass.getName());
                if (propertyMap == null || propertyMap.isEmpty()) {
                    propertyMap = new HashMap<>();
                    typeClassFieldCache.put(typeClass.getName(), propertyMap);

                    Map<ExcelCell, Map<String, String>> propertyFormatMap = new HashMap<>();
                    typeClassFieldFormatCache.put(typeClass.getName(), propertyFormatMap);
                    //
                    setPropertyMap(typeClass, propertyMap, propertyFormatMap);
                    // Field[] fields = getAllFields(typeClass);
                    // for (Field field : fields) {
                    // ExcelCell excelCell = field.getDeclaredAnnotation(ExcelCell.class);
                    // if (excelCell != null) {
                    // field.setAccessible(true);
                    // propertyMap.put(excelCell, field);
                    // if (excelCell.format() != null && excelCell.format().length > 0) {
                    // Map<String, String> map = new HashMap<>();
                    // propertyFormatMap.put(excelCell, map);
                    // for (String format : excelCell.format()) {
                    // String[] dict = format.split(":");
                    // map.put("_value_" + dict[1], dict[0]);
                    // map.put("_key_" + dict[0], dict[1]);
                    // }
                    // }
                    // }
                    // }
                }
            }
        }
        return propertyMap;
    }

    /**
     *
     * @param typeClass
     * @param propertyMap
     * @param propertyFormatMap
     */
    private static void setPropertyMap(Class typeClass, Map<ExcelCell, Field> propertyMap,
        Map<ExcelCell, Map<String, String>> propertyFormatMap) {
        if (typeClass == null) {
            return;
        }
        Field[] fields = getAllFields(typeClass);
        for (Field field : fields) {
            ExcelCell excelCell = field.getDeclaredAnnotation(ExcelCell.class);
            if (excelCell != null) {
                field.setAccessible(true);
                propertyMap.put(excelCell, field);
                // 格式化
                if (excelCell.format().length > 0) {
                    Map<String, String> map = new HashMap<>();
                    propertyFormatMap.put(excelCell, map);
                    for (String format : excelCell.format()) {
                        String[] dict = format.split(":");
                        map.put("_value_" + dict[1], dict[0]);
                        map.put("_key_" + dict[0], dict[1]);
                    }
                }
                if (excelCell.isParent()) {
                    Class<?> type = field.getType();
                    setPropertyMap(type, propertyMap, propertyFormatMap);
                }
            }
        }
    }

    public static <T extends ExcelRow> Map<ExcelCell, Map<String, String>> getFieldFormatMap(Class<T> typeClass)
        throws InstantiationException, IllegalAccessException, ClassNotFoundException {
        Map<ExcelCell, Map<String, String>> propertyFormatMap = typeClassFieldFormatCache.get(typeClass.getName());
        if (propertyFormatMap == null) {
            getFieldMap(typeClass);
            propertyFormatMap = typeClassFieldFormatCache.get(typeClass.getName());
        }
        return propertyFormatMap;
    }

    public static <T extends ExcelRow> Map<String, Integer> getTitleMap(Sheet sheet, Class<T> typeClass)
        throws ClassNotFoundException {
        Map<String, Integer> titleMap = new HashMap<String, Integer>();
        Excel excel = Class.forName(typeClass.getName()).getAnnotation(Excel.class);
        if (excel != null && excel.titleRow() >= 0) {
            Row row = sheet.getRow(excel.titleRow());
            if (row == null) {
                return titleMap;
            }
            int lastCellNum = row.getLastCellNum();
            for (int i = 0; i < lastCellNum; i++) {
                Cell cell = row.getCell(i);
                // String sCellValue = formatCell(cell);
                if (cell == null || StringUtils.isBlank(cell.getStringCellValue())) {
                    throw new BusinessException("标题为空");
                }
                String cellValue = cell.getStringCellValue().trim();
                if (titleMap.containsKey(cellValue)) {
                    throw new BusinessException("列名 " + cellValue + "重复");
                }
                titleMap.put(cellValue, i);
            }
        }

        return titleMap;
    }

    public static <T extends ExcelRow> void checkTemplate(Map<String, Integer> titleMap, Class<T> typeClass) {
        List<Field> list = Arrays.asList(getAllFields(typeClass));

        List<String> allTitle = new ArrayList<String>();
        for (int i = 0; i < list.size(); i++) {
            Field field = list.get(i);
            if (field.isAnnotationPresent(ExcelCell.class)) {// 是否使用ExcelCell注解
                for (Annotation anno : field.getDeclaredAnnotations()) {// 获得所有的注解
                    if (anno.annotationType().equals(ExcelCell.class)) {// 找到自己的注解
                        String title = ((ExcelCell)anno).title();
                        if (StringUtils.isNotBlank(title)) {
                            allTitle.add(title);
                            if (titleMap.get(title) == null) {
                                throw new BusinessException("模板错误");
                            }
                        }

                    }
                }
            }
        }
    }

    /**
     * 获取对象所有的属性
     *
     * @param clazz
     * @return
     */
    public static Field[] getAllFields(Class clazz) {
        List<Field> fieldList = new ArrayList<>();
        while (clazz != null) {
            fieldList.addAll(new ArrayList<>(Arrays.asList(clazz.getDeclaredFields())));
            clazz = clazz.getSuperclass();
        }
        Field[] fields = new Field[fieldList.size()];
        fieldList.toArray(fields);
        return fields;
    }

    public static <T extends ExcelRow> T parseRow(Row row, Class<T> typeClass, Map<ExcelCell, Field> propertyMap,
        Map<ExcelCell, Map<String, String>> propertyFormatMap, Map<String, Integer> titleMap)
        throws InstantiationException, IllegalAccessException {
        T t = typeClass.newInstance();
        t.setRowNo(row.getRowNum() + 1);

        try {
            for (Entry<ExcelCell, Field> entry : propertyMap.entrySet()) {
                ExcelCell excelCell = entry.getKey();
                Field field = entry.getValue();

                int cellNum;

                if (!"".equals(excelCell.title())) {
                    Integer index = titleMap.get(excelCell.title());
                    if (index == null) {
                        throw new BusinessException("title: " + excelCell.title() + " does not exist");
                    }
                    cellNum = index;
                } else if (excelCell.index() >= 0) {
                    cellNum = excelCell.index();
                } else {
                    throw new BusinessException("property: " + field.getName() + " not config title or index");
                }

                Cell cell = row.getCell(cellNum);

                try {
                    if (cell != null) {
                        try {
                            setValue(cell, entry, t, propertyFormatMap);
                        } catch (BusinessException e) {
                            throw new BusinessException("第" + (cellNum + 1) + "列" + excelCell.title() + e.getMessage());
                        } catch (Exception e) {
                            throw new BusinessException("第" + (cellNum + 1) + "列" + excelCell.title() + "解析失败");
                        }
                    } else {
                        if (excelCell.require()) {
                            throw new BusinessException("第" + (cellNum + 1) + "列" + excelCell.title() + "不能为空");
                        }
                    }
                } catch (BusinessException e) {
                    if (t.getErrorMessage() == null) {
                        t.setErrorMessage(e.getMessage());
                    } else {
                        t.setErrorMessage(t.getErrorMessage() + " | " + e.getMessage());
                    }
                }
            }

            /*
             * if (dataList.contains(t)) { Integer rowNo = dataList.stream().filter(e ->
             * e.equals(t)).findFirst().get().getRowNo(); throw new BusinessException("第" +
             * (i + 1) + "行和第" + rowNo + "行数据重复"); }
             */
        } catch (BusinessException e) {
            t.setErrorMessage(e.getMessage());
        }
        return t;
    }

    public static void writeErrorMessage(Sheet sheet, List<? extends ExcelRow> dataList, Map<String, Integer> titleMap,
        Workbook workbook) {

        int lastCellNum = 0;
        if (titleMap != null) {
            for (Entry<String, Integer> entry : titleMap.entrySet()) {
                if (entry.getValue() != null) {
                    if (entry.getValue().intValue() > lastCellNum) {
                        lastCellNum = entry.getValue().intValue();
                    }
                }
            }
        }

        CellStyle statusStyle = workbook.createCellStyle();
        statusStyle.setFillForegroundColor(IndexedColors.GREEN.getIndex());
        statusStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);

        CellStyle errorStyle = workbook.createCellStyle();
        errorStyle.setFillForegroundColor(IndexedColors.RED.getIndex());
        errorStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);

        for (ExcelRow excelRow : dataList) {
            if (excelRow.getErrorMessage() != null) {
                Row row = sheet.getRow(excelRow.getRowNo() - 1);
                Cell cellStatus = row.createCell(lastCellNum + 1, CellType.STRING);
                Cell cellError = row.createCell(lastCellNum + 2, CellType.STRING);
                cellStatus.setCellValue("失败");
                cellError.setCellValue(excelRow.getErrorMessage());
                cellStatus.setCellStyle(errorStyle);
            } else {
                Row row = sheet.getRow(excelRow.getRowNo() - 1);
                Cell cellStatus = row.createCell(lastCellNum + 1, CellType.STRING);
                cellStatus.setCellValue("成功");
                cellStatus.setCellStyle(statusStyle);
            }
        }
    }

    private static <T> T setValue(Cell cell, Entry<ExcelCell, Field> entry, T t,
        Map<ExcelCell, Map<String, String>> propertyFormatMap) {
        ExcelCell excelCell = entry.getKey();
        Field field = entry.getValue();
        Object value = null;

        Map<String, String> propertyFormatEntry = null;
        propertyFormatEntry = propertyFormatMap.getOrDefault(excelCell, Collections.emptyMap());

        if (cell.getCellType().equals(CellType.STRING) || (cell.getCellType().equals(CellType.FORMULA)
            && cell.getCachedFormulaResultType().equals(CellType.STRING))) {
            String cellValue = cell.getStringCellValue();
            if (excelCell.format().length > 0) {
                String formatValue = propertyFormatEntry.get("_value_" + cellValue);
                if (formatValue != null) {
                    cellValue = formatValue;
                } else if (excelCell.require()) {
                    throw new BusinessException("解析失败");
                }
            } else if (excelCell.matches().length > 0) {
                boolean flag = false;
                for (String str : excelCell.matches()) {
                    if (str.equals(cellValue)) {
                        flag = true;
                        break;
                    }
                }
                if (!flag) {
                    throw new BusinessException("解析失败");
                }
            }

            if (field.getType().equals(String.class)) {
                value = cellValue;
            } else if (field.getType().equals(int.class) || field.getType().equals(Integer.class)) {
                value = Integer.parseInt(cellValue);
            } else if (field.getType().equals(float.class) || field.getType().equals(Float.class)) {
                value = Float.parseFloat(cellValue);
            } else if (field.getType().equals(double.class) || field.getType().equals(Double.class)) {
                value = Double.parseDouble(cellValue);
            } else if (field.getType().equals(long.class) || field.getType().equals(Long.class)) {
                value = Long.parseLong(cellValue);
            } else if (field.getType().equals(BigDecimal.class)) {
                value = new BigDecimal(String.valueOf(cellValue));
            } else if (field.getType().equals(Date.class)) {
                if (excelCell.dateFormats() == null || excelCell.dateFormats().length == 0) {
                    throw new BusinessException("解析失败");
                } else {
                    try {
                        value = DateUtils.parse(cellValue, excelCell.dateFormats());
                    } catch (ParseException e) {
                        throw new BusinessException("解析失败");
                    }
                }
            } else {
                throw new BusinessException("解析失败");
            }
        } else if (cell.getCellType().equals(CellType.NUMERIC) || (cell.getCellType().equals(CellType.FORMULA)
            && cell.getCachedFormulaResultType().equals(CellType.NUMERIC))) {
            boolean dateFlag =
                DateUtil.isADateFormat(cell.getCellStyle().getDataFormat(), cell.getCellStyle().getDataFormatString());
            if (dateFlag) {
                Date date = DateUtil.getJavaDate(cell.getNumericCellValue());
                if (field.getType().equals(String.class)) {
                    value = DateUtils.FORMAT_DATETIME1.format(date);
                } else if (field.getType().equals(Date.class)) {
                    value = date;
                } else if (field.getType().equals(long.class) || field.getType().equals(Long.class)) {
                    value = date.getTime();
                } else {
                    throw new BusinessException("解析失败");
                }
            } else {
                if (field.getType().equals(String.class)) {
                    cell.setCellType(CellType.STRING);
                    value = cell.getStringCellValue();
                    if (excelCell.format().length > 0) {
                        String formatValue = propertyFormatEntry.get("_value_" + value);
                        if (formatValue != null) {
                            value = formatValue;
                        } else if (excelCell.require()) {
                            throw new BusinessException("解析失败");
                        }
                    }
                } else if (field.getType().equals(int.class) || field.getType().equals(Integer.class)) {
                    value = (int)cell.getNumericCellValue();
                    String formatValue = propertyFormatEntry.get(value);
                    if (formatValue != null) {
                        value = formatValue;
                    }
                } else if (field.getType().equals(float.class) || field.getType().equals(Float.class)) {
                    value = (float)cell.getNumericCellValue();
                } else if (field.getType().equals(double.class) || field.getType().equals(Double.class)) {
                    value = cell.getNumericCellValue();
                } else if (field.getType().equals(long.class) || field.getType().equals(Long.class)) {
                    value = (long)cell.getNumericCellValue();
                } else if (field.getType().equals(BigDecimal.class)) {
                    value = BigDecimal.valueOf(cell.getNumericCellValue());
                } else {
                    throw new BusinessException("解析失败");
                }
            }
        } else if (cell.getCellType().equals(CellType.BOOLEAN) || (cell.getCellType().equals(CellType.FORMULA)
            && cell.getCachedFormulaResultType().equals(CellType.BOOLEAN))) {
            boolean cellValue = cell.getBooleanCellValue();
            if (field.getType().equals(String.class)) {
                value = Boolean.toString(cellValue);
                if (excelCell.format().length > 0) {
                    String formatValue = propertyFormatEntry.get("_value_" + value);
                    if (formatValue != null) {
                        value = formatValue;
                    } else if (excelCell.require()) {
                        throw new BusinessException("解析失败");
                    }
                }
            } else if (field.getType().equals(boolean.class) || field.getType().equals(Boolean.class)) {
                value = cellValue;
            } else {
                throw new BusinessException("解析失败");
            }
        } else if (cell.getCellType().equals(CellType.BLANK)) {// 空的时候不需要转换
            // if (excelCell.format().length > 0 || excelCell.matches().length > 0) {
            // throw new BusinessException("解析失败");
            // }
        } else {
            throw new BusinessException("解析失败");
        }

        // 去掉首尾空格
        if (value instanceof String) {
            value = value.toString().trim();
        }

        if (excelCell.require()
            && (value == null || (value instanceof String && !StringUtils.isNotBlank(value.toString())))) {
            throw new BusinessException("不能为空");
        }

        ReflectionUtils.setField(field, t, value);

        return t;
    }

    /**
     * @param excelExportConsumer
     *            非代理的对象
     * @return
     */
    public static <T extends ExcelRow> Class<T> getTypeClass(IExcelExportConsumer<T> excelExportConsumer) {
        Type[] interfaces = excelExportConsumer.getClass().getGenericInterfaces();
        for (Type type : interfaces) {
            if ("com.unittec.drainage.provider.excel.IExcelExportConsumer"
                .equals(((ParameterizedType)type).getRawType().getTypeName())) {
                return (Class<T>)((ParameterizedType)type).getActualTypeArguments()[0];
            }
        }
        return null;
    }

    /**
     * @param excelImportConsumer
     *            代理的对象
     * @return
     */
    public static <T extends ExcelRow> Class<T> getTypeClass1(IExcelImportConsumer<T> excelImportConsumer) {
        Type[] interfaces = ((Class<?>)excelImportConsumer.getClass().getGenericSuperclass()).getGenericInterfaces();
        for (Type type : interfaces) {
            if (IExcelImportConsumer.class.equals(((ParameterizedType)type).getRawType())) {
                return (Class<T>)((ParameterizedType)type).getActualTypeArguments()[0];
            }
        }
        return null;
    }

    public static void clearData(Sheet sheet, int dataStartIndex) {
        int lastRowNum = sheet.getLastRowNum();
        if (lastRowNum >= dataStartIndex) {
            for (int i = dataStartIndex; i <= lastRowNum; i++) {
                Row row = sheet.getRow(i);
                sheet.removeRow(row);
            }
        }
    }

    /**
     *
     * @param cell
     * @return
     */
    public static String formatCell(Cell cell) {
        String value = "";
        if (cell == null) {
            return value;
        }
        switch (cell.getCellType()) {
            case NUMERIC: // 数字
                // 如果为时间格式的内容
                if (HSSFDateUtil.isCellDateFormatted(cell)) {
                    // 注：format格式 yyyy-MM-dd hh:mm:ss 中小时为12小时制，若要24小时制，则把小h变为H即可，yyyy-MM-dd HH:mm:ss
                    value = DateUtils.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue()),
                        DateUtils.COMMON_FORMAT_STR);
                    break;
                } else {
                    value = Double.toString(cell.getNumericCellValue());
                }
                break;
            case STRING: // 字符串
                value = cell.getStringCellValue();
                break;
            case BOOLEAN: // Boolean
                value = cell.getBooleanCellValue() + "";
                break;
            case FORMULA: // 公式
                value = cell.getCellFormula() + "";
                break;
            case BLANK: // 空值
                value = "";
                break;
            case ERROR: // 故障
                value = "非法字符";
                break;
            default:
                value = "未知类型";
                break;
        }
        return value;
    }

    /**
     * 单元格添加下拉菜单(不限制菜单可选项个数)<br/>
     * [注意：此方法会添加隐藏的sheet，可调用getDataSheetInDropMenuBook方法获取用户输入数据的未隐藏的sheet]<br/>
     * [待添加下拉菜单的单元格 -> 以下简称：目标单元格]
     *
     * @param @param
     *            workbook
     * @param @param
     *            tarSheet 目标单元格所在的sheet
     * @param @param
     *            menuItems 下拉菜单可选项数组
     * @param @param
     *            firstRow 第一个目标单元格所在的行号(从0开始)
     * @param @param
     *            lastRow 最后一个目标单元格所在的行(从0开始)
     * @param @param
     *            column 待添加下拉菜单的单元格所在的列(从0开始)
     */
    public static void addDropDownList(Workbook workbook, Sheet tarSheet, String[] menuItems, int firstRow, int lastRow,
        int column, int sheetNum) throws Exception {
        if (null == workbook) {
            throw new Exception("workbook为null");
        }
        if (null == tarSheet) {
            throw new Exception("待添加菜单的sheet为null");
        }
        String hiddenName = "hidden_" + (int)((Math.random() * 9 + 1) * 100);
        Sheet sheet = workbook.getSheetAt(0);
        Sheet hidden = workbook.createSheet(hiddenName);
        Cell cell = null;
        for (int i = 0, length = menuItems.length; i < length; i++) {
            String name = menuItems[i];
            Row row = hidden.createRow(i);
            cell = row.createCell(0);
            cell.setCellValue(name);
        }

        Name namedCell = workbook.createName();
        namedCell.setNameName(hiddenName);
        namedCell.setRefersToFormula(hiddenName + "!$A$1:$A$" + menuItems.length);
        // 设置下拉框数据
        DataValidationHelper helper = sheet.getDataValidationHelper();
        DataValidationConstraint constraint =
            helper.createFormulaListConstraint(hiddenName + "!$A$1:$A$" + menuItems.length);
        // 设置数据有效性加载在哪个单元格上,四个参数分别是：起始行、终止行、起始列、终止列
        CellRangeAddressList addressList = new CellRangeAddressList(firstRow, lastRow, column, column);
        DataValidation dataValidation = helper.createValidation(constraint, addressList);
        // Excel兼容性问题
        if (dataValidation instanceof XSSFDataValidation) {
            dataValidation.setSuppressDropDownArrow(true);
            dataValidation.setShowErrorBox(true);
        } else {
            dataValidation.setSuppressDropDownArrow(false);
        }
        // 将sheet设置为隐藏
        workbook.setSheetHidden(sheetNum, true);
        sheet.addValidationData(dataValidation);
    }

    /**
     * 从调用addDropDownList后添加下拉菜单的Workbook中获取用户输入数据的shee列表
     *
     * @param book
     * @return
     */
    public static List<HSSFSheet> getDataSheetInDropMenuBook(HSSFWorkbook book) {
        return getUnHideSheets(book);
    }

    /**
     * 获取所有未隐藏的sheet
     *
     * @param book
     * @return
     */
    public static List<HSSFSheet> getUnHideSheets(HSSFWorkbook book) {
        List<HSSFSheet> ret = new ArrayList<>();
        if (null == book) {
            return ret;
        }
        int sheetCnt = book.getNumberOfSheets();
        for (int i = 0; i < sheetCnt; i++) {
            if (!book.isSheetHidden(i)) {
                ret.add(book.getSheetAt(i));
            }
        }
        return ret;
    }

}
